package com.student.grade.repository;

import com.student.common.entity.Grade;
import com.student.grade.dto.GradeDTO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;

/**
 * 成绩数据访问接口
 * 
 * @author student
 * @date 2025
 */
@Repository
public interface GradeRepository extends JpaRepository<Grade, Long> {
    
    /**
     * 检查是否已存在成绩记录
     */
    boolean existsByStudentIdAndCourseIdAndSemester(String studentId, String courseId, String semester);
    
    /**
     * 根据学生ID查询成绩
     */
    Page<Grade> findByStudentId(String studentId, Pageable pageable);
    
    /**
     * 根据学生ID和学期查询成绩
     */
    Page<Grade> findByStudentIdAndSemester(String studentId, String semester, Pageable pageable);
    
    /**
     * 根据课程ID查询成绩
     */
    Page<Grade> findByCourseId(String courseId, Pageable pageable);
    
    /**
     * 根据课程ID和学期查询成绩
     */
    Page<Grade> findByCourseIdAndSemester(String courseId, String semester, Pageable pageable);
    
    /**
     * 统计指定学期的成绩数量
     */
    long countBySemester(String semester);
    
    /**
     * 根据教师ID查询成绩
     */
    Page<Grade> findByTeacherId(String teacherId, Pageable pageable);
    
    /**
     * 根据教师ID和学期查询成绩
     */
    Page<Grade> findByTeacherIdAndSemester(String teacherId, String semester, Pageable pageable);
    
    /**
     * 根据条件查询成绩记录
     */
    @Query("SELECT g FROM Grade g WHERE " +
           "(:studentId IS NULL OR g.studentId LIKE %:studentId%) AND " +
           "(:courseId IS NULL OR g.courseId LIKE %:courseId%) AND " +
           "(:semester IS NULL OR g.semester = :semester) AND " +
           "(:gradeType IS NULL OR g.gradeType = :gradeType)")
    Page<Grade> findByConditions(@Param("studentId") String studentId, 
                                @Param("courseId") String courseId, 
                                @Param("semester") String semester,
                                @Param("gradeType") String gradeType,
                                Pageable pageable);
    
    /**
     * 根据条件查询成绩记录（带关联信息）
     */
    @Query(value = "SELECT g.grade_id, g.student_id, s.name as student_name, g.course_id, c.course_id as course_code, c.course_name, " +
           "g.teacher_id, g.score, g.grade_type, g.semester, g.input_time, g.create_time, g.update_time " +
           "FROM grade g " +
           "LEFT JOIN students s ON g.student_id = s.student_id " +
           "LEFT JOIN course c ON g.course_id = c.course_id " +
           "WHERE (:studentId IS NULL OR g.student_id LIKE CONCAT('%', :studentId, '%')) AND " +
           "(:courseId IS NULL OR g.course_id LIKE CONCAT('%', :courseId, '%')) AND " +
           "(:semester IS NULL OR g.semester = :semester) AND " +
           "(:gradeType IS NULL OR g.grade_type = :gradeType)",
           countQuery = "SELECT COUNT(*) FROM grade g " +
           "LEFT JOIN students s ON g.student_id = s.student_id " +
           "LEFT JOIN course c ON g.course_id = c.course_id " +
           "WHERE (:studentId IS NULL OR g.student_id LIKE CONCAT('%', :studentId, '%')) AND " +
           "(:courseId IS NULL OR g.course_id LIKE CONCAT('%', :courseId, '%')) AND " +
           "(:semester IS NULL OR g.semester = :semester) AND " +
           "(:gradeType IS NULL OR g.grade_type = :gradeType)",
           nativeQuery = true)
    List<Object[]> findByConditionsWithDetailsRaw(@Param("studentId") String studentId, 
                                                  @Param("courseId") String courseId, 
                                                  @Param("semester") String semester,
                                                  @Param("gradeType") String gradeType,
                                                  Pageable pageable);
    
    /**
     * 统计符合条件的成绩记录数量
     */
    @Query(value = "SELECT COUNT(*) FROM grade g " +
           "LEFT JOIN students s ON g.student_id = s.student_id " +
           "LEFT JOIN course c ON g.course_id = c.course_id " +
           "WHERE (:studentId IS NULL OR g.student_id LIKE CONCAT('%', :studentId, '%')) AND " +
           "(:courseId IS NULL OR g.course_id LIKE CONCAT('%', :courseId, '%')) AND " +
           "(:semester IS NULL OR g.semester = :semester) AND " +
           "(:gradeType IS NULL OR g.grade_type = :gradeType)",
           nativeQuery = true)
    long countByConditions(@Param("studentId") String studentId, 
                          @Param("courseId") String courseId, 
                          @Param("semester") String semester,
                          @Param("gradeType") String gradeType);
}
